import collections
from tool import *
import openpyxl
from autoWidth import CXlAutofit
import matplotlib.pyplot as plt
import matplotlib.ticker as mtick
import statistics
import os
import warnings

warnings.filterwarnings('ignore')
plt.rcParams['font.sans-serif'] = ['Arial Unicode MS']

# df.to_csv('out.csv', index=False)
if __name__ == '__main__':
    yearFlag = True

    outputPrefix = '../output/'
    outputFileName = '../output/clean1.xlsx'
    prefix = '../data/'
    fileNames = os.listdir(prefix)
    # 起始行
    outRows = 1
    # 起始列
    outColumns = 1
    openyxl_createFile(outputFileName)
    wb = openpyxl.load_workbook(outputFileName)
    sheet = wb['Sheet']
    storeAndExpireTimeDict = calculateStoreAndExpireTime('../expireTimeAndStoreFileUpload/storeAndExpireTime.xlsx')
    # 启动之前删掉所有文件
    while removeFigList('../fig/', os.listdir('../fig/')):
        pass
    for finishIdx, fileNameDir in enumerate(fileNames):
        # print(fileNameDir)
        startYear = 1999
        startMonth = 1
        notTime = datetime.now()
        endYear = int(notTime.year)
        endMonth = int(notTime.month)

        # 跳过隐藏文件
        if fileNameDir.startswith('.'):
            continue
        filename = prefix + fileNameDir
        dfData = disposeData(filename)
        mydf = dfData.copy()
        mydf = mydf.iloc[::-1]
        outColumns = 1
        dic, description, maxKey, minkey, billDicForTable, saleAndCostList = cleanData(dfData, startYear, startMonth,
                                                                                       endYear, endMonth)
        description = findDescription(mydf)
        # 读取输出的文件
        minArr = minkey.split('-')
        maxArr = maxKey.split('-')
        # 数据表格中的日期起始
        startYear, startMonth, endYear, endMonth = int(minArr[0]), int(minArr[1]), int(maxArr[0]), int(maxArr[1])
        store = 0
        expireTime = 0
        if description not in storeAndExpireTimeDict.keys():
            print(
                '请检查库存及保质期文件中，没有商品名为:' + description + '，的商品，已经按默认值库存为0，保质期为0个月计算')
        else:
            curStoreAndExprireTime = storeAndExpireTimeDict[description]
            store = curStoreAndExprireTime['store']
            expireTime = curStoreAndExprireTime['expireTime']
        tableColumns = ColumnPadding(startYear, startMonth, endYear, endMonth)
        reverseTableColumns = tableColumns.copy()
        reverseTableColumns.reverse()
        locationDic = getLocationName()
        tableOneColumns = ['Invoice', 'Percent/%', 'Bill', 'Inventory Qty Adjust', 'Credit Memo', 'real time inventory']
        Bill = dic['Bill']
        billFirst = Bill.copy()
        Invoice = dic['Invoice']
        invoiceFirst = Invoice.copy()
        adjust = dic['Inventory Qty Adjust']
        adjustFirst = adjust.copy()
        creditMemo = dic['Credit Memo']
        creditMemoFirst = creditMemo.copy()
        # 库存
        storeList, storeDict = recoverStore(Invoice, Bill, reverseTableColumns, store)
        # 把上面四个的值的值转换成List -> 画图用， 填表用的是dic
        InvoiceList = floatToInt(getListFromDic(Invoice, tableColumns))
        BillList = floatToInt(getListFromDic(Bill, tableColumns))
        percentList = getPercent(InvoiceList, sum(BillList))
        percentDic = getPercentDic(percentList, tableColumns)
        invoiceAverage = calculateAverage(invoiceFirst.values())
        invoiceValueList = invoiceFirst.values()
        invoiceFirst['a-total'] = sum(invoiceFirst.values())
        invoiceFirst['a-average'] = invoiceAverage
        invoiceFirst['a-median'] = calculateMedia(invoiceValueList)
        billValueList = billFirst.values()
        billFirst['a-total'] = sum(billFirst.values())
        # billFirst['a-median'] = calculateMedia(billValueList)
        percentValueList = percentDic.values()
        percentDic['a-total'] = round(sum(percentDic.values()), 2)
        # percentDic['a-median'] = calculateMedia(percentValueList)
        creditMemoFirst['a-total'] = sum(creditMemoFirst.values())
        adjustFirst['a-total'] = sum(adjustFirst.values())
        # storeDict['a-median'] = calculateMedia(storeDict.values())
        dataOneDic = {'Invoice': invoiceFirst, 'Bill': billFirst, 'Percent/%': percentDic,
                      'Inventory Qty Adjust': adjustFirst,
                      'Credit Memo': creditMemoFirst, 'real time inventory': storeDict}

        tableColumnsFirst = tableColumns.copy()
        tableColumnsFirst.append('a-total')
        tableColumnsFirst.append('a-average')
        tableColumnsFirst.append('a-median')
        # 第二部分->保质期
        figName1 = '../fig/' + fileNameDir.split('.')[0] + str(1) + '.jpg'
        drawOne(InvoiceList, 'Invoice', BillList, 'Bill', storeList, '库存', percentList, '百分比',
                columnList=tableColumns, isDrawOne=True, figName=figName1, color1='#91ace0', color2='pink',
                color3='#c81d31', color4='#c8e5b3')
        fillTable(sheet, outColumns, outRows, description, tableOneColumns, tableColumnsFirst, dataOneDic, locationDic,
                  'fce4d3')
        outRows += len(tableOneColumns) + 4
        sheet.merge_cells(
            locationDic[outColumns] + str(outRows) + ':' + locationDic[outColumns + 1] + str(outRows))
        optionYxl(sheet, locationDic[outColumns] + str(outRows), '剩余保质期' + '(保质期：' + str(expireTime) + '个月)', font2)
        outRows += 1
        storeDic = calculateStoreDate(store, expireTime, Bill, reverseTableColumns)
        # 填写保质期表格
        fillRemainTimeTable(sheet, outRows, outColumns, locationDic, storeDic)
        outRows += len(storeDic.keys()) + 3
        insertFigToExcel(sheet, locationDic[outColumns], outRows - 1, figName1)
        outRows += 32
        # 拿图片数据  图片1 数据-》 InvoiceList ,BillList  PercentList storeList

        # 第三部分,重新计算行数
        saleOrigin = saleAndCostList['sale']
        costOrigin = saleAndCostList['cost']
        saleDicForTable3 = saleOrigin.copy()
        costDicForTable3 = costOrigin.copy()
        timeLineForTableThreeLeft = tableColumns.copy()
        timeLineForTableThreeLeft.append('a-total')
        profitDic = {}
        for idx, key in enumerate(saleDicForTable3.keys()):
            if key in costDicForTable3.keys():
                cost = costDicForTable3[key]
            else:
                cost = 0
            profitDic[key] = saleDicForTable3[key] - cost
        profitDicOrigin = profitDic.copy()
        profitTotal = round(sum(profitDic.values()), 2)
        profitPercent = {}
        for profitKey in profitDic.keys():
            profitPercent[profitKey] = dividMyself(profitDic[profitKey], saleOrigin[profitKey], True)
        profitDic['a-total'] = profitTotal
        saleDicForTable3['a-total'] = round(sum(saleDicForTable3.values()), 2)
        costDicForTable3['a-total'] = round(sum(costDicForTable3.values()), 2)
        profitPercent['a-total'] = round(calculateAverage(profitPercent.values()), 2)

        # 第三张表格位置
        outColumns = 1
        tableThreeColumnList = ['销售总额/月', '成本总额/月', '利润/月', 'Inventory Qty Adjust', 'Credit Memo',
                                '利润占比']
        dataThreeDic = {'销售总额/月': saleDicForTable3, '成本总额/月': costDicForTable3, '利润/月': profitDic,
                        'Inventory Qty Adjust': adjustFirst,
                        'Credit Memo': creditMemoFirst, '利润占比': profitPercent}
        timeLineListTableThree = tableColumns.copy()
        timeLineListTableThree.append('a-total')
        fillTable(sheet, outColumns, outRows, description + '--销售额', tableThreeColumnList,
                  timeLineListTableThree,
                  dataThreeDic, locationDic)

        outRows += len(tableThreeColumnList) + 4
        # 拿到画图数据
        saleFigList = floatToInt(getListFromDic(saleOrigin, tableColumns))
        costFigList = floatToInt(getListFromDic(costOrigin, tableColumns))
        profitFigList = floatToInt(getListFromDic(profitDicOrigin, tableColumns))
        profitPercentList = floatToInt(getListFromDic(profitPercent, tableColumns))
        figName2 = '../fig/' + fileNameDir.split('.')[0] + str(2) + '.jpg'
        drawOne(saleFigList, '销售额', costFigList, '成本额', profitFigList, '利润', profitPercentList, '利润占比',
                columnList=tableColumns, figName=figName2, isDrawOne=True, color1='#91ace0', color2='pink',
                color3='#c81d31', color4='green')

        insertFigToExcel(sheet, locationDic[outColumns], outRows, figName2)
        outRows += 32

        # 第四部分 确定最新的行列数,插完图片要重新计算行数
        outColumns = 1
        averageSale = {}
        averageCost = {}
        averageProfit = {}
        averageProfitPercent = {}
        for idx, dateTime in enumerate(tableColumns):
            averageSale[dateTime] = dividMyself(saleOrigin[dateTime], Invoice[dateTime])
            averageCost[dateTime] = dividMyself(costOrigin[dateTime], Invoice[dateTime])
            averageProfit[dateTime] = round(averageSale[dateTime] - averageCost[dateTime], 2)
        for idx, dateTime in enumerate(averageProfit.keys()):
            averageProfitPercent[dateTime] = dividMyself(averageProfit[dateTime], averageSale[dateTime], True)
        # 画图原始数据
        averageSaleOrigin = averageSale.copy()
        averageCostOrigin = averageCost.copy()
        averageProfitOrigin = averageProfit.copy()
        timeLineForTableThreeRight = tableColumns.copy()
        timeLineForTableThreeRight.append('a-年平均售价/成本价')
        tableFourColumnList = ['平均销售价/月', '平均成本价/月', '平均单价利润/月', '利润占比']
        dataFourDic = {'平均销售价/月': averageSale, '平均成本价/月': averageCost, '平均单价利润/月': averageProfit,
                       '利润占比': averageProfitPercent}
        averageSale['a-年平均售价/成本价'] = round(calculateAverage(averageSale.values()), 2)
        averageCost['a-年平均售价/成本价'] = round(calculateAverage(averageCost.values()), 2)
        averageProfit['a-年平均售价/成本价'] = round(calculateAverage(averageProfit.values()), 2)
        averageProfitPercent['a-年平均售价/成本价'] = round(calculateAverage(averageProfitPercent.values()),
                                                            2)
        fillTable(sheet, outColumns, outRows, description + '--销售价', tableFourColumnList,
                  timeLineForTableThreeRight,
                  dataFourDic, locationDic, titleColor='ffc000')
        outRows += len(tableFourColumnList) + 4

        # 月平均销售额两个表格之间的相对位置，使右边表格相对居中
        averageSaleOriginList = floatToInt(getListFromDic(averageSaleOrigin, tableColumns))
        averageCostOriginList = floatToInt(getListFromDic(averageCostOrigin, tableColumns))
        averageProfitOriginList = floatToInt(getListFromDic(averageProfitOrigin, tableColumns))
        averageProfitPercentList = floatToInt(getListFromDic(averageProfitPercent, tableColumns))
        figName3 = '../fig/' + fileNameDir.split('.')[0] + str(3) + '.jpg'
        drawOne(averageSaleOriginList, '月平均销售额', averageCostOriginList, '月平均成本额', averageProfitOriginList,
                '月平均利润', averageProfitPercentList, '利润占比', columnList=tableColumns, isDrawOne=True,
                figName=figName3, color1='#91ace0', color2='pink', color3='#c81d31', color4='green')
        insertFigToExcel(sheet, locationDic[outColumns], outRows, figName3)
        outRows += 32
        if finishIdx % 10 == 0 and finishIdx != 0:
            print('已完成' + str(finishIdx) + '个文件')
    wb.save(outputFileName)
    # clx = CXlAutofit()
    # clx.style_excel(excel_name=outputFileName, sheet_name='Sheet')
    # 结束之前也删一次
    while removeFigList('../fig/', os.listdir('../fig/')):
        pass

    print('文件已经处理完毕，请在output目录下进行查阅')
